package com.djx.comm.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

import static org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted;

/**
 * @ClassName ExcelUtils
 * @description Excel表格解析工具
 * @Author: dingjunxiong
 * @Time ：2020年10月12日
 */
public class ExcelUtils {
    private ExcelUtils() {
        throw new IllegalStateException("ExcelUtils class");
    }
    /** 记录日志 */
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    private static final String CALIBRI = "Calibri";
    private static final String UTF8 = "UTF-8";

    /**
     * 根据MultipartFile 解析Excel内容
     * @param file
     * @param startRow
     * @param sheetIndex
     * @return
     * @throws Exception
     */
    public static List<List<String>> readExcel(MultipartFile file, int startRow, int sheetIndex) {
        List<List<String>> dataLst = new ArrayList<>();
        try {
            Workbook workbook = getWb(file);
            dataLst = read(workbook, startRow, sheetIndex);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return dataLst;
    }

    /**
     * 根据Workbook 解析Excel内容
     * @param workbook 文件
     * @param startRow 开始行数
     * @param sheetIndex 下标数
     * @return
     * @throws Exception
     */
    public static List<List<String>> readExcel(Workbook workbook, int startRow, int sheetIndex) {
        List<List<String>> dataLst = read(workbook, startRow, sheetIndex);
        return dataLst;
    }

    /**
     * 解析Excel信息
     * @param wb
     * @param startRow 从第几行开始读取
     * @param sheetIndex 读取的sheet标签 从0开始
     * @return
     */
    private static List<List<String>> read(Workbook wb, int startRow, int sheetIndex) {
        /** 总列数 */
        int totalCells = 0;
        /** 创建集合存储读取的数据  */
        List<List<String>> dataLst = new ArrayList<>();
        /** 得到第sheetIndex个shell页签 */
        Sheet sheet = wb.getSheetAt(sheetIndex);
        /** 得到Excel当前页签的行数  */
        int totalRows = sheet.getPhysicalNumberOfRows();
        if (totalRows < 1) {
            return dataLst;
        }
        /** 得到Excel的列数 标题列数(具体看Excel填写) */
        totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        /** 循环Excel的行  */
        for (int r = startRow; ; r++) {
            Row row = sheet.getRow(r);
            if (null == row) {
                break;
            }
            List<String> rowLst = new ArrayList<>();
            /** 循环Excel的列 */
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {
                    /** 以下是判断数据的类型 */
                    switch (cell.getCellTypeEnum()) {
                        case NUMERIC:
                            // 数字
                            /** 判断是不是日期格式 */
                            if (isCellDateFormatted(cell)) {
                                cellValue = cell.getDateCellValue().toString();
                            }else {
                                cellValue = String.valueOf(BigDecimal.valueOf(cell.getNumericCellValue()));
                            }
                            break;
                        case STRING:
                            // 字符串
                            cellValue = cell.getStringCellValue();
                            break;
                        case BOOLEAN:
                            // Boolean
                            cellValue = cell.getBooleanCellValue() + "";
                            break;
                        case FORMULA:
                            // 公式
                            cellValue = cell.getCellFormula() + "";
                            break;
                        case BLANK:
                            // 空值
                            cellValue = "";
                            break;
                        case ERROR:
                            // 故障
                            cellValue = "非法字符";
                            break;
                        default:
                            cellValue = "未知类型";
                            break;
                    }
                }
                rowLst.add(cellValue);
            }
            /** 保存第r行的第c列 */
            boolean isEmptyRow = true;

            if (rowLst != null) {
                for (String s : rowLst) {
                    if (s != null && !s.isEmpty()) {
                        isEmptyRow = false;
                    }
                }
            }
            if (!isEmptyRow) {
                dataLst.add(rowLst);
            }
        }
        return dataLst;
    }

    /**
     * 将数据写入Excel工作簿
     * @param header    表格的标题
     * @param dataList  所需写入的数据 List<List<String>>
     * @param isExcel2003   是否是excel2003还是更高的版本
     * @param sheetName     生成的excel中sheet的名字
     * @return  Workbook对象，之后直接写出即可，如workbook.write(new FileOutputStream("E://test/20190410_test.xlsx"));
     * @throws Exception
     */
    public static Workbook getWorkbookFromList(List<String> header, List<List<String>> dataList, boolean isExcel2003, String sheetName) {
        Workbook wb;
        // 创建Workbook对象(excel的文档对象)
        if (isExcel2003) {
            wb = new HSSFWorkbook();
        } else {
            wb = new XSSFWorkbook();
        }
        // 建立新的sheet对象（excel的表单）
        Sheet sheet = wb.createSheet(sheetName);
        // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        AtomicInteger rowNum = new AtomicInteger(0);
        Row row0 = sheet.createRow(rowNum.get());
        if (!CollectionUtils.isEmpty(header)) {
            // 设置表头
            for (int i = 0; i < header.size(); i++) {
                Cell cell = row0.createCell(i);
                // 设置单元格样式
                cell.setCellStyle(getCellStyle(wb, CALIBRI, (short) 12, IndexedColors.GREY_40_PERCENT, header.get(i)));
                sheet.setColumnWidth(i, 256 * 20);
                cell.setCellValue(header.get(i));
            }
            rowNum.getAndIncrement();
        }
        if (!CollectionUtils.isEmpty(dataList)) {
            // 填充数据
            dataList.stream().forEach(data -> {
                // 创建row对象
                Row row = sheet.createRow(rowNum.get());
                for (int i = 0; i < data.size(); i++) {
                    String value = data.get(i);
                    // 遍历获取list里每一个值
                    Cell cell = row.createCell(i);
                    cell.setCellStyle(getCellStyle(wb, CALIBRI, (short) 12, null, value));
                    if (CollectionUtils.isEmpty(header)) {
                        sheet.setColumnWidth(i, 256 * 20);
                    }
                    cell.setCellValue(value);
                }
                rowNum.getAndIncrement();
            });
        }
        return wb;
    }

    /**
     * 将数据写入Excel工作簿 实体类写入
     * @param header    表格的标题
     * @param dataList  所需写入的数据 List<Object>
     * @param isExcel2003   是否是excel2003还是更高的版本
     * @param sheetName     生成的excel中sheet的名字
     * @return  Workbook对象，之后直接写出即可，如workbook.write(new FileOutputStream("E://test/20190410_test.xlsx"));
     * @throws Exception
     */
    public static Workbook getWorkbookFromObj(List<String> header, List<?> dataList, boolean isExcel2003, String sheetName) {
        Workbook wb;
        // 创建Workbook对象(excel的文档对象)
        if (isExcel2003) {
            wb = new HSSFWorkbook();
        } else {
            wb = new XSSFWorkbook();
        }
        // 建立新的sheet对象（excel的表单）
        Sheet sheet = wb.createSheet(sheetName);
        // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        int rowNum = 0;
        Row row0 = sheet.createRow(rowNum);
        if (!CollectionUtils.isEmpty(header)) {
            // 设置表头
            for (int i = 0; i < header.size(); i++) {
                Cell cell = row0.createCell(i);
                // 设置单元格样式
                cell.setCellStyle(getCellStyle(wb, CALIBRI, (short) 12, IndexedColors.GREY_40_PERCENT, header.get(i)));
                sheet.setColumnWidth(i, 256 * 20);
                cell.setCellValue(header.get(i));
            }
            rowNum++;
        }
        if (!CollectionUtils.isEmpty(dataList)) {
            // 填充数据
            /** 获取类型类 */
            Class<? extends Object> objClass = dataList.get(0).getClass();
            /** 获取所有字段 */
            Field[] fields = objClass.getDeclaredFields();
            for (int i = 0; i < dataList.size(); i++) {
                // 创建row对象
                Row row = sheet.createRow(rowNum);
                // 遍历获取每一个字段的值
                for (int j = 0; j < fields.length; j++) {
                    String fieldVal = "";
                    /** 获取所有方法 */
                    Method[] methods = objClass.getDeclaredMethods();
                    for (Method method : methods) {
                        if (method.getName().equalsIgnoreCase("get" + fields[j].getName())) {
                            String property = null;
                            try {
                                property = (String) method.invoke(dataList.get(i), null);
                            } catch (IllegalAccessException e) {
                                logger.error(e.getMessage(), e);
                            } catch (InvocationTargetException e) {
                                logger.error(e.getMessage(), e);
                            }
                            fieldVal = property == null ? "" : property;
                            break;
                        }
                    }
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(getCellStyle(wb, CALIBRI, (short) 12, null, fieldVal));
                    if (CollectionUtils.isEmpty(header)) {
                        sheet.setColumnWidth(j, 256 * 20);
                    }
                    cell.setCellValue(fieldVal);
                }
                rowNum++;
            }
        }
        return wb;
    }

    /**
     * 设置单元格的边框（细）且为黑色，字体水平垂直居中，自动换行
     * @param workbook
     * @param fontName 字体名称
     * @param fontSize 字体大小
     * @param color 填充颜色
     * @param value 填写信息
     * @return
     */
    public static CellStyle getCellStyle(Workbook workbook, String fontName, short fontSize, IndexedColors color, String value){
        CellStyle style = workbook.createCellStyle();
        DataFormat dataFormat = workbook.createDataFormat();
        Font font = workbook.createFont();
        // 水平左对齐，垂直居中，自动换行
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(false);
        // 设置背景色
        if (color != null) {
            //表头居中
            if (color.equals(IndexedColors.GREY_40_PERCENT) || color.equals(IndexedColors.SKY_BLUE)) {
                style.setAlignment(HorizontalAlignment.CENTER);
            }
            //添加颜色
            style.setFillForegroundColor(color.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        // 设置上下左右四个边框宽度
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        // 设置上下左右四个边框颜色
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        // 设置字体样式及大小
        font.setFontName(fontName);
        font.setFontHeightInPoints(fontSize);
        if (StringUtils.isNotBlank(value) && value.contains("*")) {
            //带星号则文字标红
            font.setColor(IndexedColors.RED.getIndex());
        }
        style.setFont(font);
        style.setDataFormat(dataFormat.getFormat("@"));
        return style;
    }

    /**
     * 下载文件
     * @param fileName
     * @param response
     * @param workbook
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.reset();
            response.setCharacterEncoding(UTF8);
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, UTF8));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
    }

    /**
     * 下载文件
     * @param fileName
     * @param response
     * @param inputStream 数据流
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, InputStream inputStream) {
        try (OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())){
            response.reset();
            response.setCharacterEncoding(UTF8);
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, UTF8));
            //创建存放文件内容的数组
            byte[] buff = new byte[1024];
            //所读取的内容使用n来接收
            int n;
            //当没有读取完时,继续读取,循环
            while ((n = inputStream.read(buff)) != -1) {
                //将字节数组的数据全部写入到输出流中
                outputStream.write(buff, 0, n);
            }
            //强制将缓存区的数据进行输出
            outputStream.flush();
            //关流
            inputStream.close();
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
    }

    /**
     * Excel 转 InputStream
     * @param workbook
     * @return
     */
    public static InputStream excelToByteArray(Workbook workbook) {
        try (ByteArrayOutputStream bos = new ByteArrayOutputStream()){
            workbook.write(bos);
            InputStream is = new ByteArrayInputStream(bos.toByteArray());
            return is;
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        return null;
    }

    /**
     * 创建Excel到指定目录
     * @param url 指定目录
     * @param fileName 文件名称
     * @param workbook excel表格
     */
    public static void createExcel(String url, String fileName, Workbook workbook) {
        File file =new File(url);
        //如果文件夹不存在则创建
        if  (!file.exists() && !file.isDirectory()) {
            file.mkdirs();
        }
        try {
            FileOutputStream output = new FileOutputStream(url + fileName);
            workbook.write(output);
        } catch (FileNotFoundException e) {
            logger.error(e.getMessage(), e);
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
    }

    public static Workbook getWb(InputStream is, String fileName){
        Workbook workbook = null;
        try {
            //https://my.oschina.net/u/3681868/blog/2253908
            //延迟解析比率
            ZipSecureFile.setMinInflateRatio(-1.0d);
            if (Boolean.TRUE.equals(MatchesUtils.verifyExcel(fileName))) {
                /** Excel 2007 */
                workbook = new XSSFWorkbook(is);
            } else {
                /** Excel 2003 */
                workbook = new HSSFWorkbook(is);
            }
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        return workbook;
    }

    public static Workbook getWb(MultipartFile file){
        Workbook workbook = null;
        try (InputStream is = file.getInputStream()) {
            if (Boolean.TRUE.equals(MatchesUtils.verifyExcel(file.getOriginalFilename()))) {
                /** Excel 2007 */
                workbook = new XSSFWorkbook(is);
            } else {
                /** Excel 2003 */
                workbook = new HSSFWorkbook(is);
            }
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        return workbook;
    }

    /**
     * 获取Excel表格标签总数
     * @param workbook
     * @return
     */
    public static int getSheetNum(Workbook workbook){
        return workbook.getNumberOfSheets();
    }

    /**
     * 获取指定第i个标签名称
     * @param workbook
     * @param i
     * @return
     */
    public static String getSheetName(Workbook workbook, int i){
        return workbook.getSheetName(i);
    }

}
